using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using MacomberMap.Common.Components;
using System.IO;
using System.Drawing;
using System.Data;
using MacomberMap.Common.Internals;
using MacomberMap.Common.Types;
using System.Drawing.Imaging;
using MacomberMap.Common.User_Interfaces.Components;
using System.Reflection;
using MacomberMap.Common.User_Interfaces.Database;
using System.ComponentModel;
using MacomberMap.Common.Events;
using MacomberMap.Common.Logging;
using System.IO.Compression;
using Oracle.DataAccess.Client;

namespace MacomberMap.Common.Database_Interface
{
    /// <summary>
    /// This class provides linkages to the CIM Oracle database
    /// </summary>
    public class MM_Database_Oracle : MM_Database
    {
        #region Variable declarations
        /// <summary>The connection to the oracle database</summary>
        public OracleConnection oConn;

        /// <summary>The user name to connect to the database with</summary>
        private string Username;

        /// <summary>The password to connect to the database with</summary>
        private string Password;

        /// <summary>The database entry connect to the database with</summary>
        private string Database;

        /// <summary>The string to be used for connecting to the Oracle database</summary>
        public String ConnectionString;

        /// <summary>
        /// Report the type of database this is.
        /// </summary>
        public override string Type
        {
            get { return "Oracle"; }
            set
            {
                if (value != "Oracle")
                    throw new InvalidDataException("An incorrect database type was passed to the Oracle database initializer: " + value + "!");
            }
        }

        /// <summary>The Oracle Transaction</summary>
        private OracleTransaction oTrans = null;
        #endregion

        #region Database Searching
        /// <summary>
        /// Load the TNS names file, parse it out, and display all available databases.
        /// </summary>
        /// <param name="DatabaseCombos">The database combo boxes to modify</param>
        public static void LoadOracleDatabases(params ComboBox[] DatabaseCombos)
        {
            //First, clear the combo boxes
            foreach (ComboBox cmb in DatabaseCombos)
                cmb.Items.Clear();

            //Go through all directories in the Path, and locate our TNSNames.ora file.
            StreamReader sRd = null;
            FileInfo fI = null;
            String[] PathsToSearch = Environment.GetEnvironmentVariable("Path").Split(';');
            foreach (String SearchPath in PathsToSearch)
                if ((fI = new FileInfo(SearchPath + @"\..\Network\Admin\TnsNames.ora")).Exists)
                    sRd = new StreamReader(fI.OpenRead());

            if (sRd == null)
                MessageBox.Show("Unable to locate the TNSNAMES.ORA file in the system path!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            else
            {
                //Now go through the TNSNAMES.ORA file one line at a time, find lines with 0 parentheses depths that don't start with # (comment), and add to the list.
                Stack<Char> Parens = new Stack<Char>();
                String inLine;
                StringBuilder FoundNames = new StringBuilder();

                while ((inLine = sRd.ReadLine()) != null)
                    if ((inLine.Trim().Length > 0) && (inLine.Trim()[0] != '#'))
                        foreach (Char c in inLine.ToCharArray())
                            if (c == '(')
                                Parens.Push(c);
                            else if (c == ')')
                                Parens.Pop();
                            else if (Parens.Count == 0)
                                FoundNames.Append(c);

                //Close the file reader
                sRd.Close();

                //Now go through our found data, and handle accordingly.
                foreach (String TNSName in FoundNames.ToString().Split('='))
                    if (TNSName.Trim().Length > 0)
                        foreach (ComboBox cmb in DatabaseCombos)
                            cmb.Items.Add(TNSName.Trim().ToLowerInvariant());
            }
        }
        #endregion

        #region Initialization
        /// <summary>
        /// Initialize an oracle database from a connection string
        /// </summary>
        /// <param name="Username">The username of the database</param>
        /// <param name="Password">The password for the database</param>
        /// <param name="Database">The database's TNS entry</param>
        /// <param name="Name">The name of the database</param>
        public MM_Database_Oracle(String Username, String Password, String Database, String Name)
            : base(Name)
        {
            this.Username = Username;
            this.Password = Password;
            this.Database = Database;
        }

        /// <summary>
        /// Initialize an oracle database from XML
        /// </summary>
        /// <param name="BaseElement"></param>
        public MM_Database_Oracle(XmlElement BaseElement)
            : base(BaseElement)
        {
            this.Username = BaseElement.Attributes["Username"].Value;
            this.Password = BaseElement.Attributes["Password"].Value;
            this.Database = BaseElement.Attributes["Database"].Value;
        }

        /// <summary>
        /// Save this connection's XML configuration
        /// </summary>
        /// <param name="xW"></param>
        public override void SaveConfigurationParameters(XmlTextWriter xW)
        {
            xW.WriteStartElement("Database");
            xW.WriteAttributeString("Username", Username);
            xW.WriteAttributeString("Password", Password);
            xW.WriteAttributeString("Database", Database);
            if (this == MM_Configuration.DefaultDatabase)
                xW.WriteAttributeString("Default", "true");
        }
        #endregion

        #region Connectivity
        /// <summary>
        /// Open the connection to the Oracle database
        /// </summary>
        public override void Open()
        {
            if (oConn == null)
            {
                oConn = new OracleConnection(this.ConnectionString = String.Format("User ID={0}; Password={1}; Data Source={2};", Username, Password, Database));
                oConn.InfoMessage += new OracleInfoMessageEventHandler(oConn_InfoMessage);                
            }        
            oConn.Open();
        }

        /// <summary>
        /// Close the connection to the Oracle database
        /// </summary>
        public override void Close()
        {
            if (oConn != null)
                oConn.Close();
        }

        /// <summary>
        /// Handle informational messages from the server.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void  oConn_InfoMessage(object sender, OracleInfoMessageEventArgs e)
        {
            Console.WriteLine("Oracle message: " + e.Message);
        }

        /// <summary>
        /// Return the current connection status
        /// </summary>
        public override System.Data.ConnectionState State
        {
            get
            {
                if (oConn == null)
                    return System.Data.ConnectionState.Closed;
                else
                    return oConn.State;
            }
        }

        /// <summary>Display the connection statistics for the connected database</summary>        
        public override DataTable ConnectionStats()
        {
            OracleDataAdapter oAdap = new OracleDataAdapter("SELECT a.Table_Name, b.Comments, a.Status, a.Num_Rows, a.Avg_Row_Len,  a.Num_Rows * a.Avg_Row_Len AS Est_Size, a.Logging, a.Backed_Up from user_tables a, user_tab_comments b where a.table_name=b.table_name order by a.Table_Name", oConn);
            DataTable inStats = new DataTable();
            oAdap.Fill(inStats);
            return inStats;            
        }
        #endregion

        #region Lat/Long retrieval and saving
        /// <summary>
        /// Load our counties into memory
        /// </summary>
        public override void LoadCounties()
        {
            MM_Repository.Counties.Clear();
            OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_BOUNDARY", oConn);
            OracleDataReader oRd = oCmd.ExecuteReader();
            while (oRd.Read())
                new MM_Boundary(oRd);
        }

        /// <summary>
        /// Load a list of counties in from a .CSV file (in the format [County name],[Website]
        /// </summary>
        /// <param name="Filename">The county website filename</param>
        public override void SaveCountyWebsites(string Filename)
        {
            //Read the file in line by line.
            StreamReader sRd = new StreamReader(Filename);
            while (!sRd.EndOfStream)
            {
                String[] inStr = sRd.ReadLine().Split(new char[] { ',' }, 2);
                //If we already have this county, update its website. Otherwise, add it in.
                if (MM_Repository.Counties.ContainsKey(inStr[0]))
                    MM_Repository.Counties[inStr[0]].Website = inStr[1];
                else
                {
                    MM_Boundary NewBound = new MM_Boundary();
                    NewBound.Name = inStr[0];
                    NewBound.Website = inStr[1];
                    NewBound.Max = NewBound.Min = NewBound.Centroid = new PointF(float.NaN, float.NaN);
                    MM_Repository.Counties.Add(NewBound.Name, NewBound);
                }
            }

            //Now, save our county information
            SaveCounties();
        }

        /// <summary>
        /// Save a boundary to the Oracle database
        /// </summary>
        /// <param name="County">The county to be saved</param>
        public override void SaveBoundary(MM_Boundary County)       
        {
            using (OracleCommand oCmd = new OracleCommand("REPLACE INTO MM_BOUNDARY (NAME, MAX_X, MAX_Y, MIN_X, MIN_Y, CENTROID_X, CENTROID_Y, WEBSITE, COORDINATES) VALUES (:NAME, :MAX_X, :MAX_Y, :MIN_X, :MIN_Y, :CENTROID_X, :CENTROID_Y, :WEBSITE, :COORDINATES)", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("NAME", County.Name);
                oCmd.Parameters.Add("MAX_X", County.Max.X);
                oCmd.Parameters.Add("MAX_Y", County.Max.Y);
                oCmd.Parameters.Add("MIN_X", County.Min.X);
                oCmd.Parameters.Add("MIN_Y", County.Max.Y);
                oCmd.Parameters.Add("CENTROID_X", County.Centroid.X);
                oCmd.Parameters.Add("CENTROID_Y", County.Centroid.Y);
                oCmd.Parameters.Add("WEBSITE", County.Website);
                MemoryStream OutStream = new MemoryStream(County.Coordinates.Length * 8);
                foreach (PointF pt in County.Coordinates)
                {
                    OutStream.Write(BitConverter.GetBytes(pt.X), 0, 4);
                    OutStream.Write(BitConverter.GetBytes(pt.Y), 0, 4);
                }
                oCmd.Parameters.Add("WEBSITE", OutStream.GetBuffer());
                OutStream.Dispose();
                oCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Save a map tile to the Oracle database
        /// </summary>
        /// <param name="NewTile">The new tile to be uploaded</param>
        public override void SaveMapTile(MM_MapTile NewTile)
        {
            OracleCommand oCmd = new OracleCommand("REPLACE INTO MM_MAPTILE (TYPE, X, Y, ZOOM, IMAGE) VALUES (:TYPE, :X, :Y, :ZOOM, :IMAGE)", oConn);
            oCmd.Prepare();
            oCmd.Parameters.Add("TYPE", (int)NewTile.mapType);
            oCmd.Parameters.Add("X", NewTile.TileXY.X);
            oCmd.Parameters.Add("Y", NewTile.TileXY.Y);
            oCmd.Parameters.Add("ZOOM", NewTile.ZoomLevel);
            using (MemoryStream mS = new MemoryStream())
            {
                NewTile.Tile.Save(mS, ImageFormat.Png);
                oCmd.Parameters.Add("IMAGE", mS.GetBuffer());
                oCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Load a map tile from the Oracle database
        /// </summary>
        /// <param name="mapType">The map type to be used</param>
        /// <param name="TileXY">The coordinates of the tile</param>
        /// <param name="ZoomLevel">The zoom level of the map tile</param>
        /// <param name="Tile">The tile, if it can be retrieved</param>
        /// <returns></returns>
        public override bool LoadMapTile(MM_MapTile.MapType mapType, Point TileXY, int ZoomLevel, out Bitmap Tile)
        {
            OracleCommand oCmd = new OracleCommand("SELECT TILE FROM MM_MAPTILE WHERE TYPE=:TYPE AND X=:X and Y=:Y", oConn);
            oCmd.Prepare();
            oCmd.Parameters.Add("TYPE", (int)mapType);
            oCmd.Parameters.Add("X", TileXY.X);
            oCmd.Parameters.Add("Y", TileXY.Y);
            oCmd.Parameters.Add("ZOOM", ZoomLevel);
            OracleDataReader oRd = oCmd.ExecuteReader();
            if (oRd.HasRows)
            {
                oRd.Read();
                using (MemoryStream inMs = new MemoryStream((byte[])oRd["TILE"]))
                    Tile = new Bitmap(inMs);
                return true;
            }
            else
            {
                Tile = null;
                return false;
            }
        }

        /// <summary>
        /// Save our collection of counties
        /// </summary>
        public override void SaveCounties()
        {
            foreach (MM_Boundary County in MM_Repository.Counties.Values)
                SaveBoundary(County);
        }

        /// <summary>
        /// Save our state coordinates
        /// </summary>
        public override void SaveStateCoordinates()
        {
            SaveBoundary(MM_Repository.StateBoundary);
        }


   

       

        /// <summary>
        /// Load the trace around the element in question
        /// </summary>
        /// <param name="BaseElement">The element in question</param>
        /// <returns></returns>
        public override List<MM_Element> LoadElements(MM_Element BaseElement)
        {
            List<MM_Element> OutElements = new List<MM_Element>();
            using (OracleCommand oCmd = new OracleCommand("SELECT TEID, Trace FROM MM_TRACE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                {
                    UInt64 TEID = Convert.ToUInt64(oRd["TEID"]);
                    if (MM_Repository.TEIDs.ContainsKey(TEID))
                        OutElements.Add(MM_Repository.TEIDs[TEID]);
                    else
                    {
                        MM_Element ElemToAdd = LocateElement(TEID);
                        OutElements.Add(ElemToAdd);
                        MM_Repository.TEIDs.Add(TEID, ElemToAdd);
                    }
                }
            OutElements.TrimExcess();
            return OutElements;
        }

        /// <summary>
        /// Save our substation coordinates
        /// </summary>
        public override void SaveSubstationCoordinates()
        {
            throw new InvalidOperationException("This method is no longer implemented.");
        }
        #endregion

        #region Element updating

        /// <summary>
        /// Initiate an Oracle transaction
        /// </summary>
        public void StartTransaction()
        {
            if (oTrans == null)
            {
                lock (this)
                    oTrans = oConn.BeginTransaction();
                MessageBox.Show("Oracle Transaction initiated.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
                MessageBox.Show("Oracle Transaction has already been initiated.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// Cancel the transaction
        /// </summary>
        public void CancelTransaction()
        {
            if (oTrans != null)
            {
                lock (this)
                {
                    oTrans.Rollback();
                    oTrans.Dispose();
                    oTrans = null;
                }
                MessageBox.Show("Oracle Transaction cancelled (rolled back).", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
                MessageBox.Show("No Oracle Transaction is available for roll back.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// Commit the transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (oTrans != null)
            {
                lock (this)
                {
                    oTrans.Commit();
                    oTrans.Dispose();
                    oTrans = null;
                }
                MessageBox.Show("Oracle Transaction committed.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
                MessageBox.Show("No Oracle Transaction is available to committed.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void DisplayCursors()
        {
            using (OracleCommand oCmd = new OracleCommand("select a.value, b.name, a.sid, d.sql_text from v$sesstat a, v$statname b, v$session c, v$sql d     where a.statistic# = b.statistic# and b.name = 'opened cursors current'", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
            {
                oRd.Read();
                Console.WriteLine("Cursors: {0} {1} {2} {3}", oRd[0], oRd[1], oRd[2], oRd[3]);
                oRd.Close();
            }
        }
        #endregion

        #region Element addition and updating
        /// <summary>
        /// Add or update an element in the database
        /// </summary>
        /// <param name="ElementToAdd"></param>
        public void AddOrUpdateElement(MM_Element ElementToAdd)
        {
            //First, if the class requests, add into the elements collection
            try
            {
                if (ElementToAdd.TargetType.IsElement)
                    AddElementToElementCollection(ElementToAdd);
                if (ElementToAdd.TargetType.IsTypeSpecific)
                    AddElementToTargetCollection(ElementToAdd);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in adding/updating element: " + ex.Message);
            }
           
        }

      


        /// <summary>
        /// Add an element to the generic element collection
        /// </summary>
        /// <param name="ElementToAdd"></param>
        private void AddElementToElementCollection(MM_Element ElementToAdd)
        {
            Dictionary<String, Object> OutParameters = new Dictionary<string, object>();            
            foreach (MemberInfo mI in MM_Serializable.GetMembers(ElementToAdd))
                foreach (object obj in mI.GetCustomAttributes(true))
                    if (obj is MM_Serializable_Target && (obj as MM_Serializable_Target).IsElement)
                        if (mI is FieldInfo)
                            OutParameters.Add(mI.Name, (mI as FieldInfo).GetValue(ElementToAdd));
                        else if (mI is PropertyInfo)
                            OutParameters.Add(mI.Name, (mI as PropertyInfo).GetValue(ElementToAdd, null));

            //Now, add the index
            KeyValuePair<String, Object> Index = new KeyValuePair<string, object>(null, null);
            foreach (MemberInfo mI in GetMembers(ElementToAdd))
                foreach (object obj in mI.GetCustomAttributes(true))
                    if (obj is MM_Serializable_Target && (obj as MM_Serializable_Target).IsIndex)
                        if (mI is FieldInfo)
                            Index = new KeyValuePair<string,object>(mI.Name, (mI as FieldInfo).GetValue(ElementToAdd));
                        else if (mI is PropertyInfo)
                            Index = new KeyValuePair<string, object>(mI.Name, (mI as PropertyInfo).GetValue(ElementToAdd, null));

            WriteDatabase("MM_Elements", OutParameters, Index);
        }

        /// <summary>
        /// Add an element to the specific element type collection
        /// </summary>
        /// <param name="ElementToAdd"></param>
        private void AddElementToTargetCollection(MM_Element ElementToAdd)
        {
            Dictionary<String, Object> OutParameters = new Dictionary<string, object>();
            foreach (MemberInfo mI in GetMembers(ElementToAdd))
                foreach (object obj in mI.GetCustomAttributes(true))
                    if (obj is MM_Serializable_Target && (obj as MM_Serializable_Target).IsTypeSpecific)
                        if (mI is FieldInfo)
                            OutParameters.Add(mI.Name, (mI as FieldInfo).GetValue(ElementToAdd));
                        else if (mI is PropertyInfo)
                            OutParameters.Add(mI.Name, (mI as PropertyInfo).GetValue(ElementToAdd, null));

            //Now, add the index
            KeyValuePair<String, Object> Index=new KeyValuePair<string,object>(null,null);
            foreach (MemberInfo mI in GetMembers(ElementToAdd))
                foreach (object obj in mI.GetCustomAttributes(true))
                    if (obj is MM_Serializable_Target && (obj as MM_Serializable_Target).IsIndex)
                        if (mI is FieldInfo)
                            Index = new KeyValuePair<string, object>(mI.Name, (mI as FieldInfo).GetValue(ElementToAdd));
                        else if (mI is PropertyInfo)
                            Index = new KeyValuePair<string, object>(mI.Name, (mI as PropertyInfo).GetValue(ElementToAdd, null));
            if (Index.Key.Equals("Index", StringComparison.CurrentCultureIgnoreCase))
                Index = new KeyValuePair<string, object>("\"INDEX\"", Index.Value);
           
            if (ElementToAdd.GetType().Name == "MM_Element")
                WriteDatabase("MM_" + ElementToAdd.ElemType.Name, OutParameters, Index);
            else
                WriteDatabase(ElementToAdd.GetType().Name, OutParameters, Index);
        }

        /// <summary>
        /// Update an element's value
        /// </summary>
        /// <param name="Element"></param>
        /// <param name="ValueName"></param>
        /// <param name="NewValue"></param>
        public void UpdateElement(MM_Element Element, String ValueName, Object NewValue)
        {
            //Create our dictionary with updated value
            Dictionary<String, Object> Parameters = new Dictionary<string, object>();
            MemberInfo mVal = Element.GetType().GetMember(ValueName)[0];
            if (mVal is FieldInfo)
                Parameters.Add(ValueName, (mVal as FieldInfo).GetValue(Element));
            else if (mVal is PropertyInfo)
                Parameters.Add(ValueName, (mVal as PropertyInfo).GetValue(Element,null));
            MM_Serializable_Target tVal = null;
            foreach (Object obj in mVal.GetCustomAttributes(true))
                if (obj is MM_Serializable_Target)
                    tVal = obj as MM_Serializable_Target;

            //Create our index key value pair
            KeyValuePair<String,Object> Index = new KeyValuePair<string,object>(null,null);
            foreach (MemberInfo mI in Element.GetType().GetMembers())
            foreach (Object obj in mI.GetCustomAttributes(true))
                if (obj is MM_Serializable_Target && (obj as MM_Serializable_Target).IsIndex)
                    if (mI is FieldInfo)
                    Index = new KeyValuePair<string,object>(mI.Name, (mI as FieldInfo).GetValue(Element));
                    else if (mI is PropertyInfo)
                        Index = new KeyValuePair<string,object>(mI.Name, (mI as PropertyInfo).GetValue(Element,null));

            //Based on the results, update the index and/or database
            if (tVal.IsIndex && Element.TargetType.IsIndex)
                WriteDatabase("MM_Elements", Parameters, Index);
            if (tVal.IsTypeSpecific && Element.TargetType.IsTypeSpecific)
                WriteDatabase(Element.GetType().Name, Parameters, Index);
        }

        /// <summary>
        /// Write data out to the database
        /// </summary>
        /// <param name="TableName">The table name</param>
        /// <param name="Parameters"></param>
        /// <param name="Index"></param>
        private void WriteDatabase(String TableName, Dictionary<String, Object> Parameters, KeyValuePair<String, Object> Index)
        {
                    
            //First, build an insert function
            StringBuilder sB = new StringBuilder();
            bool FirstKey = true;
            foreach (KeyValuePair<String, Object> kvp in Parameters)
            {
                sB.Append((FirstKey ? "INSERT INTO " + TableName + "(" : ", ") + kvp.Key);
                FirstKey = false;
            }
            sB.Append((Index.Value != null ? ", " + Index.Key : "") + ") values ");
            FirstKey = true;
            foreach (KeyValuePair<String, Object> kvp in Parameters)
            {
                sB.Append((FirstKey ? "(:" : ", :") + kvp.Key);
                FirstKey = false;
            }

            sB.Append((Index.Value != null ? ", :" + Index.Key : "") + ")");

            //Prepare to execute our insert function
            OracleCommand oCmd = new OracleCommand(sB.ToString(), oConn);
            oCmd.CommandType = CommandType.Text;
            oCmd.Prepare();

            foreach (KeyValuePair<String, Object> kvp in Parameters)
            {
                Object inValue = MM_Serializable.ConvertValue(kvp.Value);
                if (inValue is byte[])
                {
                    OracleParameter oParam = oCmd.Parameters.Add(kvp.Key, OracleDbType.Blob);
                    oParam.Direction = ParameterDirection.Input;
                    oParam.Value = inValue;
                    oParam.Size = ((Byte[])inValue).Length;
                }
                else
                    oCmd.Parameters.Add(kvp.Key, inValue);
            }


            if (Index.Value != null)
                oCmd.Parameters.Add(Index.Key, MM_Serializable.ConvertValue(Index.Value));

            //Attempt to execute the insert
            try
            {
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
            catch (Exception ex)
            {
                if (ex is OracleException && (ex as OracleException).ErrorCode == 1)
                {
                    //If we weren't able to insert due to a duplicate record, change the statement to an update.
                    sB.Remove(0, sB.Length);
                    FirstKey = true;
                    foreach (KeyValuePair<String, Object> kvp in Parameters)
                    {
                        sB.Append((FirstKey ? "   UPDATE " + TableName + " set " : ", ") + kvp.Key + "=:" + kvp.Key);
                        FirstKey = false;
                    }
                    sB.Append(" where " + TableName + "." + Index.Key + "=:" + Index.Key);

                    try
                    {
                        oCmd.CommandText = sB.ToString();
                        using (OracleDataReader oRd = oCmd.ExecuteReader())
                            oRd.Close();
                    }
                    catch (Exception ex2)
                    {
                        Console.WriteLine("Error in row update: " + ex2.Message);
                    }
                }
                else
                    Console.WriteLine("Error in row insert: " + ex.Message);


            }
            oCmd.Dispose();
        }

        /// <summary>
        /// Update an element's TEID
        /// </summary>
        /// <param name="ElementToUpdate"></param>
        /// <param name="OldTEID"></param>
        /// <param name="NewTEID"></param>
        public void UpdateTEID(MM_Element ElementToUpdate, UInt64 OldTEID, UInt64 NewTEID)
        {
            //First, update the TEID of the element
            using (OracleCommand oCmd = new OracleCommand("UPDATE MM_ELEMENTS SET TEID=:NEWTEID WHERE TEID=:OLDTEID", oConn))
            {
                oCmd.Transaction = oTrans;
                oCmd.Prepare();
                oCmd.Parameters.Add("NEWTEID", NewTEID);
                oCmd.Parameters.Add("OLDTEID", OldTEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
            
            //Now, update the teid of the element
            using (OracleCommand oCmd = new OracleCommand("UPDATE MM_" + ElementToUpdate.ElemType.Name.ToUpper() + " SET TEID=:NEWTEID WHERE TEID=:OLDTEID", oConn))
            {
                oCmd.Transaction = oTrans;
                oCmd.Prepare();
                oCmd.Parameters.Add("NEWTEID", NewTEID);
                oCmd.Parameters.Add("OLDTEID", OldTEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }


        }

        

        /// <summary>
        /// Remove an element from the database
        /// </summary>
        /// <param name="ElementToRemove">The element to be removed</param>
        public void RemoveElement(MM_Element ElementToRemove)
        {
            if (ElementToRemove is MM_Zone == false)
                using (OracleCommand oCmd = new OracleCommand("DELETE FROM MM_ELEMENTS WHERE TEID=:TEID"))
                {
                    oCmd.Prepare();
                    oCmd.Parameters.Add("TEID", ElementToRemove.TEID);
                    using (OracleDataReader oRd = oCmd.ExecuteReader())
                        oRd.Close();
                }
            using (OracleCommand oCmd = new OracleCommand("DELETE FROM MM_" + ElementToRemove.ElemType.Name + " WHERE TEID=:TEID"))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("TEID", ElementToRemove.TEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
        }
        #endregion

        #region CIM loading
        /// <summary>
        /// Load in our collection of lines
        /// </summary>
        public override void LoadLines()
        {
            using (OracleCommand oCmd = new OracleCommand("SELECT TEID, NAME, OWNER, OPERATOR, KVLEVEL, SUBSTATION1, SUBSTATION2, ISSERIESCOMPENSATOR FROM MM_LINE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                {
                    MM_Line NewLine = new MM_Line(oRd);
                    if (!MM_Repository.TEIDs.ContainsKey(NewLine.TEID))
                        MM_Repository.TEIDs.Add(NewLine.TEID, NewLine);
                    if (!MM_Repository.Lines.ContainsKey(NewLine.Name))
                        MM_Repository.Lines.Add(NewLine.Name, NewLine);
                }
        }

        /// <summary>
        /// Load in our collection of substations
        /// </summary>
        public override void LoadSubstations()
        {
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_SUBSTATION", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                {
                    MM_Substation NewSub = new MM_Substation(oRd);
                    if (!MM_Repository.TEIDs.ContainsKey(NewSub.TEID))
                        MM_Repository.TEIDs.Add(NewSub.TEID, NewSub);
                    if (!MM_Repository.Substations.ContainsKey(NewSub.Name))
                        MM_Repository.Substations.Add(NewSub.Name, NewSub);
                }
        }


       


        /// <summary>
        /// Locate or create a zone with the specified name
        /// </summary>
        /// <param name="ZoneName">The name to search for</param>
        /// <param name="ZoneType">The type of zone to be located</param>
        /// <returns></returns>
        private static MM_Zone LocateZone(string ZoneName, MM_Element_Type ZoneType)
        {
            MM_Zone OutZone;
            if (String.IsNullOrEmpty(ZoneName))
                return null;
            if (!MM_Repository.Zones.TryGetValue(ZoneName, out OutZone))
            {
                MM_Zone NewZone = new MM_Zone(ZoneName, 0, ZoneType);
                MM_Repository.Zones.Add(ZoneName, NewZone);
                MM_Log.LogEvent(new MM_Event_NewElement(NewZone));
            }
            return OutZone;
        }

        /// <summary>
        /// Retrieve our DC tie information from the database, and merge them into our existing lines.
        /// </summary>
        public override void MergeDCTies()
        {
            //Now, retrieve our new information in from 
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_DCTIE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                    if (MM_Repository.TEIDs.ContainsKey(Convert.ToUInt64(oRd["ASSOCIATEDLINE"])))
                    {
                        MM_Line AssocLine = MM_Repository.TEIDs[Convert.ToUInt64(oRd["ASSOCIATEDLINE"])] as MM_Line;
                        AssocLine.KVLevel = MM_Overall_Display.KVLevels["DC Tie"];
                        AssocLine.Name = MM_Repository.TitleCase((string)oRd["NAME"]);
                    }
        }

        /// <summary>
        /// Load our KV levels from the database
        /// </summary>
        public override void LoadKVLevels()
        {
            MM_Overall_Display.KVLevels.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_KVLEVEL", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                    MM_Overall_Display.KVLevels.Add((string)oRd["Name"], new MM_KVLevel(oRd));
        }

        /// <summary>
        /// Load our element types from the database
        /// </summary>
        public override void LoadElementTypes()
        {
            MM_Overall_Display.ElemTypes.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_ELEMENT_TYPE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                    MM_Overall_Display.ElemTypes.Add(Convert.ToInt32(oRd["Index"]), (string)oRd["Name"], new MM_Element_Type(oRd));                   
        }


        /// <summary>
        /// Load our Event types from the database
        /// </summary>
        public override void LoadEventTypes()
        {
            MM_Overall_Display.EventTypes.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_Event_TYPE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                    MM_Overall_Display.EventTypes.Add((string)oRd["Name"], new MM_Event_Type(oRd));
        }
        #endregion

        #region Table building and manipulation
        /// <summary>
        /// Remove CIM data from the database
        /// </summary>
        public override void BlankDatabase()
        {            
            //Now, offer the options to the user
            int TotalRows = 0;
            using (frmTableSelector NewTable = new frmTableSelector(ConnectionStats()))
                if (NewTable.ShowDialog() == DialogResult.OK)
                    foreach (String TableToDelete in NewTable.SelectedTables)
                        TotalRows += ExecuteCommand("DELETE FROM " + TableToDelete, oConn);

            MessageBox.Show("Removed a total of " + TotalRows.ToString("#,##0") + " rows.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// Drop one or more databases
        /// </summary>
        public void DropDatabase()
        {
            int TotalRows = 0, TotalTables = 0;

            using (frmTableSelector NewTable = new frmTableSelector(ConnectionStats()))
                if (NewTable.ShowDialog() == DialogResult.OK)
                    foreach (String TableToDelete in NewTable.SelectedTables)
                    {
                        TotalRows += ExecuteCommand("DROP TABLE " + TableToDelete, oConn);
                        TotalTables++;
                    }
            MessageBox.Show("Dropped a total of " + TotalRows.ToString("#,##0") + " rows from " + TotalTables.ToString("#,##0") + " tables.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        #endregion

        #region Command execution
        /// <summary>
        /// Execute a command return the number of rows affeted
        /// </summary>
        /// <param name="CommandText">The text of the command</param>
        /// <param name="oConn">The Oracle database connection</param>
        /// <returns>The number of rows affected by the query</returns>
        private static int ExecuteCommand(String CommandText, OracleConnection oConn)
        {
            int Result;
            using (OracleCommand oCmd = new OracleCommand(CommandText, oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
            {
                Result = oRd.RecordsAffected;
                oRd.Close();
            }
            return Result;
        }
        #endregion

        #region Schema creation
        /// <summary>
        /// Create a new Oracle database schema to hold the MM loader data
        /// </summary>
        /// <param name="ConnectionName">The name of the connection</param>
        /// <param name="AdminUser">Administrator's user name</param>
        /// <param name="AdminPassword">Administrator's password</param>
        /// <param name="ServerName">Server TNS Name</param>
        /// <param name="UserName">New Schema name / User Name</param>
        /// <param name="UserPassword">New User password</param>
        /// <param name="DbSize">The current size of the database</param>
        /// <param name="MaxSize">The maximum size of the database</param>
        /// <param name="DbPath">The path to the Oracle database file</param>
        /// <param name="StepSize">The database incremental size</param>
        public static MM_Database_Oracle CreateSchema(String ConnectionName, String AdminUser, String AdminPassword, String ServerName, String UserName, String UserPassword, String DbPath, Decimal DbSize, Decimal MaxSize, Decimal StepSize)
        {
            //Create our admin-level connection
            using (OracleConnection oConnAdmin = new OracleConnection(String.Format("Data Source={0}; User ID={1}; Password={2}", ServerName, AdminUser, AdminPassword)))
            {
                oConnAdmin.Open();

                //Run our Oracle commands to set up the database
                ExecuteCommand(String.Format("CREATE USER {0} IDENTIFIED BY {1}", UserName, UserPassword), oConnAdmin);
                ExecuteCommand(String.Format("CREATE tablespace {0} datafile '{1}{0}.dbf' size {2}M autoextend on next {3}M maxsize {4}M extent management local", UserName, DbPath, DbSize, StepSize, MaxSize), oConnAdmin);
                ExecuteCommand(String.Format("GRANT CREATE SESSION TO {0}", UserName), oConnAdmin);
                ExecuteCommand(String.Format("GRANT CREATE TABLE TO {0}", UserName), oConnAdmin);
                ExecuteCommand(String.Format("GRANT CREATE TRIGGER TO {0}", UserName), oConnAdmin);
                ExecuteCommand(String.Format("GRANT CREATE SEQUENCE TO {0}", UserName), oConnAdmin);
                ExecuteCommand(String.Format("ALTER USER {0} QUOTA UNLIMITED ON {0}", UserName), oConnAdmin);
                ExecuteCommand(String.Format("ALTER USER {0} DEFAULT TABLESPACE {0}", UserName), oConnAdmin);

                //Close our admin connection, and establish our standard connection
                oConnAdmin.Close();

                //Now, build our new connection
                MM_Database_Oracle OutDb = new MM_Database_Oracle(UserName, UserPassword, ServerName, ConnectionName);
                return OutDb;
            }
        }

        /// <summary>
        /// Create new database tables
        /// </summary>
        /// <param name="DbConnection">The database connection, if already opened</param>
        public override void CreateTables(object DbConnection)
        {
            //Establish our oracle connection
            OracleConnection oConn = (DbConnection == null ? this.oConn : DbConnection as OracleConnection);

            //Drop all MM tables            
            OracleDataReader oRd = new OracleCommand("SELECT TABLE_NAME FROM USER_TABLES", oConn).ExecuteReader();
            while (oRd.Read())
                if (((string)oRd["TABLE_NAME"]).StartsWith("MM_"))
                    ExecuteCommand("DROP TABLE " + oRd["TABLE_NAME"], oConn);

            //Create our tables for CIM elements                
            AddElementTable("Breaker", "Circuit breakers", "HASSYNCHROSCOPE", "NUMBER(1,0)");
            AddElementTable("BusBarSection", "Bus bar sections","AssociatedNode","NUMBER");
            AddElementTable("Capacitor", "Capacitors (Shunt Compensators with MVAR > 0)");            
            AddElementTable("DCTie", "DC Ties", "NAME","", "NAME", "VARCHAR(50)", "TEID","","UNIT","NUMBER", "LOAD","NUMBER", "ASSOCIATEDLINE","NUMBER");
            AddElementTable("EPSMeter", "EPS Meters", "RID", "VARCHAR2(40)");
            AddElementTable("EndCap", "End Caps");
            AddElementTable("ElectricalBus", "Electrical buses", "ASSOCIATEDNODE","NUMBER");
            AddElementTable("LaaR", "Loads acting as resources");
            AddElementTable("Line", "Lines", "SUBSTATION", "", "SUBSTATION1", "NUMBER", "SUBSTATION2", "NUMBER", "COORDINATES", "BLOB","ISSERIESCOMPENSATOR","NUMBER(1,0)", "ISMULTIPLESEGMENT","NUMBER(1,0)","LINETEID","NUMBER");
            AddElementTable("Load", "Loads");
            AddElementTable("Node", "Connectivity Nodes","ConnectedElements","VARCHAR(400)","BusbarSection","NUMBER","ElectricalBus","NUMBER");
            AddElementTable("PricingVector", "Pricing Vectors", "EPSMETER", "NUMBER", "NODEELEMENT", "NUMBER", "OTHERELEMENT", "NUMBER", "ISPOSITIVE", "NUMBER(1,0)","RID","VARCHAR2(40)");
            AddElementTable("Reactor", "Reactors (Shunt Compensators with MVAR < 0)");
            AddElementTable("StaticVarCompensator", "Static Var Compensators");
            AddElementTable("Substation", "Substations", "LONGNAME", "VARCHAR(500)", "LONGITUDE", "FLOAT", "LATITUDE", "FLOAT", "COUNTY", "NUMBER", "KVLEVEL", "", "SUBSTATION", "", "KVLEVELS", "NUMBER", "ELEMTYPES", "VARCHAR2(30)", "WEATHERZONE", "NUMBER", "LOADZONE", "NUMBER");
            AddElementTable("Switch", "Switches");
            AddElementTable("Transformer", "Transformers", "KVLEVEL", "", "KVLEVEL1", "NUMBER", "KVLEVEL2", "NUMBER", "WINDING1", "NUMBER", "WINDING2", "NUMBER","PHASESHIFTER","NUMBER(1,0)");
            AddElementTable("TransformerWinding", "Transformer Windings", "TRANSFORMER", "NUMBER");
            AddElementTable("Unit", "Generators", "UNITTYPE", "VARCHAR(50)", "ISRC", "NUMBER(*,0)");

            //Now, add in the other tables
            AddOtherTable("Boundary", "State and County Boundaries", "INDEX", "INTEGER NOT NULL", "NAME", "VARCHAR2(50)", "COORDINATES", "BLOB", "MAX_X", "FLOAT", "MAX_Y", "FLOAT", "MIN_X", "FLOAT", "MIN_Y", "FLOAT", "CENTROID_X", "FLOAT", "CENTROID_Y", "FLOAT", "WEBSITE", "VARCHAR2(4000)");
            AddOtherTable("Company", "Companies (QSEs, TSPs, and other market participants", "TEID", "INTEGER NOT NULL", "NAME", "VARCHAR2(500)", "DUNS", "VARCHAR2(500)", "PRIMARYPHONE", "VARCHAR2(500)");
            AddOtherTable("Contingency", "Contingency Definitions", "TEID", "INTEGER NOT NULL", "DEFINITION", "CLOB");            
            AddOtherTable("Elements","All transmisison elements", "TEID","INTEGER NOT NULL", "ELEMTYPE", "INTEGER NOT NULL", "SUBSTATION", "INTEGER", "SUBNAME", "VARCHAR2(50)", "NAME", "VARCHAR(250)", "KVLEVEL", "NUMBER");
            ExecuteCommand("CREATE INDEX MM_IDX_ELEMENTS ON MM_ELEMENTS (TYPE, SUBNAME, NAME)", oConn);
            AddOtherTable("Event_Type", "Event Types","Index","NUMBER","NAME","VARCHAR2(50)", "ACRONYM","VARCHAR(4)", "PRIORITY","NUMBER","FORECOLOR","NUMBER","WIDTH","NUMBER","BLINK","NUMBER","EVENTPARAMETERS","CLOB","EVENTIMAGE","BLOB");
            AddOtherTable("Element_Type", "Element Types", "INDEX", "NUMBER", "NAME", "VARCHAR2(30)", "Acronym", "VARCHAR(4)", "CIMName", "VARCHAR2(50)", "CIMParameter", "VARCHAR2(200)", "ElementParameters", "CLOB");
            AddOtherTable("KVLevel", "KV Levels","Index","NUMBER","NAME","VARCHAR2(50)", "\"Energized.ForeColor\"","Number", "\"Energized.Thickness\"","Number", "\"Energized.Blink\"","Number","\"PartiallyEnergized.ForeColor\"", "Number", "\"PartiallyEnergized.Thickness\"", "Number", "\"PartiallyEnergized.Blink\"", "Number","\"DeEnergized.ForeColor\"", "Number", "\"DeEnergized.Thickness\"", "Number", "\"DeEnergized.Blink\"", "Number");            
            AddOtherTable("Notes","Notes on transmission elements", "ASSOCIATEDELEMENT","NUMBER","INDEX","NUMBER","CREATEDON","TIMESTAMP(6)","AUTHOR","VARCHAR2(100)","NOTE","CLOB");
            AddOtherTable("OneLine","One-lines","BASE_ELEMENT","NUMBER","NAME","VARCHAR2(100)","ONE_LINE","CLOB","TYPE","NUMBER","IMAGE","BLOB");
            AddOtherTable("Role","User roles", "ROLEID","NUMBER","ROLENAME","VARCHAR2(20)","PERMISSIONS","CLOB","DEFAULTVIEW","NUMBER");
            AddOtherTable("User", "Authorized MM users", "USERINDEX","NUMBER","USERNAME","VARCHAR2(20)","USERROLE","NUMBER");
            AddOtherTable("View", "Views authorized for users","INDEX","NUMBER","NAME","VARCHAR2(50)", "VIEWACCESS","VARCHAR2(20)", "VIEWPARAMS","CLOB");
            AddOtherTable("Zone", "Weather and load zones", "INDEX","NUMBER","NAME","VARCHAR2(50)", "ELEMTYPE","NUMBER");
            MessageBox.Show("Table creation complete.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        /// <summary>
        /// Add a contingency definition for an element
        /// </summary>
        /// <param name="TEID">The TEID of the element to be located</param>
        /// <param name="ContingencyToAdd">The XML of the contingency to be added.</param>
        /// <returns></returns>
        public void AddContingency(UInt64 TEID, XmlDocument ContingencyToAdd)
        {
            using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_CONTINGENCY (TEID, DEFINITION) VALUES (:TEID, :DEFINITION)", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("TEID", TEID);
                oCmd.Parameters.Add("DEFINITION", ContingencyToAdd.OuterXml);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
        }

        /// <summary>
        /// Update a contingency definition for an element
        /// </summary>
        /// <param name="TEID">The TEID of the element to be located</param>
        /// <param name="ContingencyToUpdate">The XML of the contingency to be added.</param>
        /// <returns></returns>        
        public void UpdateContingency(UInt64 TEID, XmlDocument ContingencyToUpdate)
        {
            using (OracleCommand oCmd = new OracleCommand("UPDATE MM_CONTINGENCY SET DEFINITION=:DEFINITON WHERE TEID=:TEID", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("DEFINITION", ContingencyToUpdate.OuterXml);
                oCmd.Parameters.Add("TEID", TEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
        }

        /// <summary>
        /// Load a contingency definition for an element
        /// </summary>
        /// <param name="TEID">The TEID of the element to be located</param>
        /// <returns></returns>
        public XmlDocument LoadContingency(UInt64 TEID)
        {
            XmlDocument OutDoc = null;
            using (OracleCommand oCmd = new OracleCommand("SELECT Definition from MM_CONTINGENCY where TEID=:TEID", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("TEID", TEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                {
                    if (oRd.Read())
                    {
                        OutDoc = new XmlDocument();
                        OutDoc.LoadXml(oRd["Definition"] as string);
                    }
                }
            }

            return OutDoc;
        }

        /// <summary>
        /// Add an element-based table
        /// </summary>
        /// <param name="TableName">The outgoing name of the table</param>
        /// <param name="Comment">The comment for the table</param>
        /// <param name="AdditionalColumnData">The additional column modification data</param>
        private void AddElementTable(String TableName, String Comment, params String[] AdditionalColumnData)
        {
            Dictionary<String, String> OutColumns = new Dictionary<string, string>();
            OutColumns.Add("TEID", "NUMBER");
            OutColumns.Add("NAME", "VARCHAR2(50)");
            OutColumns.Add("OWNER", "NUMBER");
            OutColumns.Add("OPERATOR", "NUMBER");
            OutColumns.Add("KVLEVEL", "NUMBER");
            OutColumns.Add("SUBSTATION", "NUMBER");

            //Now, go through the rest of our parameters
            for (int a = 0; a < AdditionalColumnData.Length; a += 2)
            {
                if (OutColumns.ContainsKey(AdditionalColumnData[a]) && String.IsNullOrEmpty(AdditionalColumnData[a + 1]))
                    OutColumns.Remove(AdditionalColumnData[a]);
                else if (OutColumns.ContainsKey(AdditionalColumnData[a]))
                    OutColumns[AdditionalColumnData[a]] = AdditionalColumnData[a + 1];
                else
                    OutColumns.Add(AdditionalColumnData[a], AdditionalColumnData[a + 1]);
            }
            AddTable(TableName, Comment, OutColumns);
        }

        /// <summary>
        /// Add another table to the database
        /// </summary>
        /// <param name="TableName">The name of the table</param>
        /// <param name="Comment">The comment for the table</param>
        /// <param name="ColumnData">The column data for the table</param>
        private void AddOtherTable(String TableName, String Comment, params String[] ColumnData)
        {
            
            
            Dictionary<String, String> OutColumns = new Dictionary<string, string>();
            for (int a = 0; a < ColumnData.Length; a += 2)
                try
                {
                    OutColumns.Add(ColumnData[a], ColumnData[a + 1]);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
        
            AddTable(TableName, Comment, OutColumns);
        }

        /// <summary>
        /// Add a table to the Oracle database
        /// </summary>
        /// <param name="TableName">The name of the table</param>
        /// <param name="Comment">The comment to include for the table</param>
        /// <param name="ColumnData">Column data within the table</param>
        private void AddTable(String TableName, String Comment, Dictionary<String, String> ColumnData)
        {
            String PrimaryKey = "";
            //Build our SQL string
            StringBuilder oString = new StringBuilder();
            try
            {
                foreach (KeyValuePair<String, String> kvp in ColumnData)
                {
                    if (String.IsNullOrEmpty(PrimaryKey))
                    {
                        PrimaryKey = kvp.Key;
                        oString.AppendFormat("CREATE TABLE MM_{0} ({1} {2}", TableName, (kvp.Key.Equals("INDEX", StringComparison.CurrentCultureIgnoreCase) ? "\"INDEX\"" : kvp.Key), kvp.Value);
                    }
                    else
                        oString.AppendFormat(", {0} {1}", (kvp.Key.Equals("INDEX", StringComparison.CurrentCultureIgnoreCase) ? "\"INDEX\"" : kvp.Key), kvp.Value);
                }
                oString.AppendFormat(", CONSTRAINT MM_{0}_PK PRIMARY KEY ( {1} ) ENABLE )", TableName, (PrimaryKey.Equals("INDEX", StringComparison.CurrentCultureIgnoreCase) ? "\"INDEX\"" : PrimaryKey));

                ExecuteCommand(oString.ToString(), oConn);
                ExecuteCommand("COMMENT ON TABLE MM_" + TableName + " IS '" + Comment + "'", oConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error writing table: " + ex.Message);
            }
            oString = null;                       
        }
        #endregion

        #region User/Role/Database lookup
        /// <summary>
        /// Look up a user in the USER table, and if found, return the appropriate role for that user
        /// </summary>
        /// <param name="UserName">The user name to be looked up</param>        
        /// <returns>-1 if the user name isn't found, otherwise the role</returns>
        public override int LookupUser(string UserName)
        {

            using (OracleCommand oCmd = new OracleCommand("SELECT USERROLE FROM MM_USER WHERE USERNAME=:USERNAME", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("USERNAME", UserName);

                using (OracleDataReader oRd = oCmd.ExecuteReader())

                    //If we can't find a user role for that user, return no role. Otherwise, return true w/ that role
                    if (!oRd.HasRows)
                        return -1;
                    else
                    {
                        oRd.Read();
                        return Convert.ToInt32(oRd["USERROLE"]);
                    }

            }
        }

        /// <summary>
        /// Load a role's display and permissions XML
        /// </summary>
        /// <param name="UserRole">The user role to be loaded</param>
        /// <param name="DefaultView">The number corresponding to the role default view</param>
        /// <param name="PermissionsXML">The permissions XML parameter</param>
        public override void LoadRole(int UserRole, out int DefaultView, out string PermissionsXML)
        {
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_ROLE WHERE ROLEID=:ROLEID", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("ROLEID", UserRole);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                {
                    oRd.Read();                   
                    PermissionsXML = (string)oRd["PERMISSIONS"];
                    DefaultView = Convert.ToInt32(oRd["DEFAULTVIEW"]);
                }
            }       
        }

        /// <summary>
        /// Load all views into memory
        /// </summary>
        /// <param name="UserRole">The user's role #</param>
        /// <param name="DefaultView">The number of the default view</param>
        public override void LoadViews(int UserRole, int DefaultView)
        {
            MM_View.Views.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_VIEW", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                    if (((string)oRd["VIEWACCESS"])[UserRole] == '1')
                    {
                        MM_View NewView = new MM_View(oRd);
                        MM_View.Views.Add(NewView.Name, NewView);
                        if (NewView.Index == DefaultView)
                        {
                            MM_View.DefaultView = NewView;
                            NewView.Activate();
                        }
                            
                    }            
        }


        /// <summary>
        /// Load weather and load zones
        /// </summary>
        public override void LoadZones()
        {
            MM_Repository.Zones.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_ZONE", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())                 
                        MM_Repository.Zones.Add(Convert.ToInt32(oRd["Index"]), MM_Repository.TitleCase((string)oRd["NAME"]), new MM_Zone(oRd));                 
        }


        /// <summary>
        /// Load our collection of companies
        /// </summary>
        public override void LoadCompanies()
        {
            MM_Repository.Companies.Clear();
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_Company", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader())
                while (oRd.Read())
                {
                    MM_Company NewCompany = new MM_Company(oRd);
                    MM_Repository.Companies.Add(NewCompany.Name, NewCompany);
                    MM_Repository.TEIDs.Add(NewCompany.TEID, NewCompany);
                }
        }

        /// <summary>
        /// Populate the user roles UI with all appropriate data 
        /// </summary>      
        public override DataSet LoadUsersAndRoles()
        {
            DataSet OutData = new DataSet("Macomber Map Permissions");
            OutData.Tables.Add("Roles");
            OutData.Tables.Add("Users");
            OutData.Tables.Add("Databases");
            using (OracleDataAdapter oAdap = new OracleDataAdapter("SELECT * FROM MM_ROLE", oConn))
                oAdap.Fill(OutData.Tables["Roles"]);
            using (OracleDataAdapter oAdap = new OracleDataAdapter("SELECT * FROM MM_USER", oConn))
                oAdap.Fill(OutData.Tables["Users"]);
            using (OracleDataAdapter oAdap = new OracleDataAdapter("SELECT * FROM MM_DATABASE", oConn))
                oAdap.Fill(OutData.Tables["Databases"]);
            return OutData;
        }
        #endregion

        #region One-line handling
        /// <summary>
        /// Load our collection of one-lines into a data table
        /// </summary>
        /// <returns></returns>
        public override DataTable LoadOneLines()
        {
            DataTable OutTable = new DataTable("OneLines");
            using (OracleDataAdapter oDa = new OracleDataAdapter("SELECT BASE_ELEMENT, NAME, TYPE FROM MM_ONELINE", oConn))
                oDa.Fill(OutTable);
            return OutTable;
        }

        /// <summary>
        /// Load a particular one-line by name
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public override string LoadOneLine(string Name)
        {
            String OutText=null;
            using (OracleCommand oCmd = new OracleCommand("SELECT ONE_LINE FROM MM_ONELINE WHERE NAME=:NAME", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("NAME", Name);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    while (oRd.Read())
                        OutText = (String)oRd[0];
            }
            return OutText;                            
        }

        /// <summary>
        /// Load a particular one-line by TEID
        /// </summary>
        /// <param name="TEID"></param>
        /// <returns></returns>
        public override string LoadOneLine(ulong TEID)
        {
            String OutText = null;
            using (OracleCommand oCmd = new OracleCommand("SELECT ONE_LINE FROM MM_ONELINE WHERE BASE_ELEMENT=:BASE_ELEMENT", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("BASE_ELEMENT", TEID);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    while (oRd.Read())
                        OutText = (String)oRd[0];
            }
            return OutText;                            
        }

        /// <summary>
        /// Upload a one-line into the database
        /// </summary>
        /// <param name="BaseElement">The base element for the one-line</param>
        /// <param name="OneLineXML">The XML defining the one-line</param>
        public override void SaveOneLine(MM_Element BaseElement, String OneLineXML)
        {
            using (OracleCommand oCmd = new OracleCommand("DELETE FROM MM_ONELINE WHERE BASE_ELEMENT=:BASE_ELEMENT AND NAME=:NAME AND TYPE=:TYPE", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("BASE_ELEMENT", BaseElement.TEID);
                oCmd.Parameters.Add("NAME", BaseElement.Name);
                oCmd.Parameters.Add("TYPE", BaseElement is MM_Substation ? 1 : 0);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
            using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_ONELINE (BASE_ELEMENT, NAME, TYPE, ONE_LINE) VALUES (:BASE_ELEMENT, :NAME, :TYPE, :ONE_LINE)", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("BASE_ELEMENT", BaseElement.TEID);
                oCmd.Parameters.Add("NAME", BaseElement.Name);
                oCmd.Parameters.Add("ONE_LINE", OneLineXML);
                oCmd.Parameters.Add("TYPE", BaseElement is MM_Substation ? 1: 0);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();                
            }
        }
        #endregion

        #region Element lookup
        /// <summary>
        /// Look up an element by TEID
        /// </summary>
        /// <param name="TEID">The TEID to search for</param>
        /// <returns></returns>
        public override MM_Element LocateElement(ulong TEID)
        {
            //First, determine our element type.
            OracleCommand oCmd = new OracleCommand("SELECT ELEMTYPE FROM MM_ELEMENTS WHERE TEID=" + TEID.ToString(), oConn);
            OracleDataReader oRd = oCmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
            if (!oRd.HasRows)
            {
                oRd.Close();
                return null;
            }
            else
            {
                oRd.Read();
                MM_Element_Type FoundType = LocateElementType(Convert.ToInt32(oRd["ELEMTYPE"]));
                oRd.Close();
                return LocateElement(TEID, FoundType);                
            }
        }

        /// <summary>
        /// Locate an element type based on its integer value
        /// </summary>
        /// <param name="TypeInt">The integer value for the type</param>
        /// <returns></returns>
        private MM_Element_Type LocateElementType(int TypeInt)
        {
            foreach (MM_Element_Type ElemType in MM_Overall_Display.ElemTypes.Values)
                if (ElemType.Index == TypeInt)
                    return ElemType;
            return null;
        }

        /// <summary>
        /// Look up an element by TEID and element type
        /// </summary>
        /// <param name="TEID">The TEID of the elemnt</param>
        /// <param name="ElemType">The type of the element</param>
        /// <returns></returns>
        public override MM_Element LocateElement(ulong TEID, MM_Element_Type ElemType)
        {
            //If our TEID is 0, return nothing.
            if (TEID == 0)
                return null;

            //First, check to see if we already have the TEID, and if so, just return that one.
            if (MM_Repository.TEIDs.ContainsKey(TEID))
                return MM_Repository.TEIDs[TEID];

            //Pull in the new element from the specified table           
            using (OracleCommand oCmd = new OracleCommand("SELECT * FROM MM_" + ElemType.Name + " WHERE TEID=" + TEID.ToString(), oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
            {
                MM_Element OutElement = null;                
                if (oRd.HasRows)
                {
                    oRd.Read();
                    OutElement = MM_Element.CreateElement(oRd, ElemType);                  
                }
                oRd.Close();
                return OutElement;
            }
        }

        

        /// <summary>
        /// Look up an element by name and TEID
        /// </summary>
        /// <param name="SubstationName">The name of the substation</param>
        /// <param name="ElemName">The name of the element</param>
        /// <param name="ElemType">The type of the element</param>
        /// <returns></returns>
        public override MM_Element LocateElement(string SubstationName, string ElemName, MM_Element_Type ElemType)
        {
            using (OracleCommand oCmd = new OracleCommand("SELECT TEID FROM MM_ELEMENTS WHERE TYPE=" + ElemType.Index + " AND SUBNAME='" + SubstationName.ToUpper() + "' AND ELEMNAME='" + ElemName.ToUpper() + "'", oConn))
            using (OracleDataReader oRd = oCmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
            if (!oRd.HasRows)
            {
                oRd.Close();
                return null;
            }
            else
            {
                oRd.Read();
                UInt64 ElemTEID = Convert.ToUInt64(oRd["TEID"]);
                oRd.Close();
                return LocateElement(ElemTEID, ElemType);
            }
        }
        #endregion

        #region Schema backup/restore
        /// <summary>
        /// Restore a schema to the database
        /// </summary>
        /// <param name="FileName"></param>
        public int RestoreSchema(string FileName)
        {
            //Load the XML document into memory
            XmlDocument xDoc = new XmlDocument();
            if (FileName.EndsWith(".xml", StringComparison.CurrentCultureIgnoreCase))
                xDoc.Load(FileName);
            else if (FileName.EndsWith(".mm_model", StringComparison.CurrentCultureIgnoreCase))
                xDoc.Load(FileName);
            else if (FileName.EndsWith(".xml.gz", StringComparison.CurrentCultureIgnoreCase))
                using (FileStream f = new FileStream(FileName, FileMode.Open))
                using (GZipStream g = new GZipStream(f, CompressionMode.Decompress))
                    xDoc.Load(g);

            //Parse through the XML document, and handle accordingly.
            int ModifiedRows = 0;
            if (FileName.EndsWith(".mm_model", StringComparison.CurrentCultureIgnoreCase))
            {
                int ViewNum = 1;
                //Go through the model, and pull in all our information, starting with views
                

                //Pull in our default view
                StringBuilder DefaultView = new StringBuilder();
                foreach (String strToAdd in "Substation,VoltageLevel,Violation".Split(','))
                    foreach (XmlElement xElem in xDoc.SelectNodes("NetworkModel/Configuration/DisplayParameters/"+strToAdd))
                        foreach (XmlAttribute xAttr in xElem.Attributes)
                            if (xAttr.Name != "Name")
                                DefaultView.AppendLine(xElem.Attributes["Name"].Value + "." + xAttr.Name + "=" + xAttr.Value);
                using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_VIEW (ID, NAME, VIEWACCESS, VIEWPARAMS) VALUES (:ID, :NAME, :VIEWACCESS, :VIEWPARAMS)", oConn))
                {
                    oCmd.Parameters.Add("ID", ViewNum++);
                    oCmd.Parameters.Add("NAME", "Default");
                    oCmd.Parameters.Add("VIEWACCESS", "1");
                    oCmd.Parameters.Add("VIEWPARAMS", DefaultView.ToString());                   
                    using (OracleDataReader oRd = oCmd.ExecuteReader())
                        ModifiedRows += oRd.RecordsAffected;
                }


                //Pull in our other views
                foreach (XmlElement xView in xDoc.SelectNodes("//View"))
                    using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_VIEW (ID, NAME, VIEWACCESS, VIEWPARAMS) VALUES (:ID, :NAME, :VIEWACCESS, :VIEWPARAMS)", oConn))
                    {
                        oCmd.Parameters.Add("ID", ViewNum++);
                        oCmd.Parameters.Add("NAME", xView.Attributes["Name"].Value);
                        oCmd.Parameters.Add("VIEWACCESS", "1");
                        StringBuilder OutLine = new StringBuilder();
                        string inLine;
                        using (StringReader sRd = new StringReader(xView.InnerText))
                            while ((inLine = sRd.ReadLine()) != null)
                                if (inLine.Trim().Length > 0)
                                    OutLine.AppendLine(inLine.Trim());                        
                                                
                        oCmd.Parameters.Add("VIEWPARAMS", OutLine.ToString());
                        XmlNode CurNode = xView;
                        while ((CurNode = CurNode.ParentNode).Name != "Views")
                            oCmd.Parameters["NAME"].Value = CurNode.Attributes["Name"].Value + "\\" + (string)oCmd.Parameters["NAME"].Value;
                        using (OracleDataReader oRd = oCmd.ExecuteReader())
                            ModifiedRows += oRd.RecordsAffected;
                    }

                //Now, pull in our boundaries
                ViewNum=0;
                foreach (XmlElement xBound in xDoc.SelectNodes("*/MM_Boundary"))
                    using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_BOUNDARY (ID, NAME, COORDINATES, MAX_X, MAX_Y, MIN_X, MIN_Y, CENTROID_X, CENTROID_Y, WEBSITE) VALUES (:ID, :NAME, :COORDINATES, :MAX_X, :MAX_Y, :MIN_X, :MIN_Y, :CENTROID_X, :CENTROID_Y, :WEBSITE)", oConn))
                    {
                        oCmd.Parameters.Add("ID", ViewNum++);
                        oCmd.Parameters.Add("NAME", xBound.Attributes["Name"].Value);
                        using (MemoryStream mS = new MemoryStream())
                        {
                            foreach (String CoordToAdd in xBound.Attributes["Coordinates"].Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                                mS.Write(BitConverter.GetBytes(float.Parse(CoordToAdd)), 0, 4);
                            oCmd.Parameters.Add("COORDINATES", mS.ToArray());
                        }
                        
                        oCmd.Parameters.Add("MAX_X", float.Parse(xBound.Attributes["Max"].Value.Split(',')[0]));
                        oCmd.Parameters.Add("MAX_Y", float.Parse(xBound.Attributes["Max"].Value.Split(',')[1]));
                        oCmd.Parameters.Add("MIN_X", float.Parse(xBound.Attributes["Min"].Value.Split(',')[0]));
                        oCmd.Parameters.Add("MIN_Y", float.Parse(xBound.Attributes["Min"].Value.Split(',')[1]));
                        oCmd.Parameters.Add("CENTROID_X", float.Parse(xBound.Attributes["Centroid"].Value.Split(',')[0]));
                        oCmd.Parameters.Add("CENTROID_Y", float.Parse(xBound.Attributes["Centroid"].Value.Split(',')[1]));
                        oCmd.Parameters.Add("Website", xBound.Attributes["Website"].Value);

                        using (OracleDataReader oRd = oCmd.ExecuteReader())
                            ModifiedRows += oRd.RecordsAffected;
                    }


                //Pull in our KV levels
                int CurVoltage = 1;
                foreach (XmlElement xKVLevel in xDoc.SelectNodes("//VoltageLevel"))
                    using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_KVLEVEL (ID, NAME, \"Energized.ForeColor\", \"Energized.Thickness\", \"Energized.Blink\", \"PartiallyEnergized.ForeColor\", \"PartiallyEnergized.Thickness\", \"PartiallyEnergized.Blink\", \"DeEnergized.ForeColor\", \"DeEnergized.Thickness\", \"DeEnergized.Blink\") VALUES (:ID, :NAME, :E1, :E2, :E3, :P1, :P2, :P3, :D1, :D2, :D3)", oConn))
                    {
                        oCmd.Parameters.Add("ID", CurVoltage);
                        CurVoltage <<= 1;
                        oCmd.Parameters.Add("NAME", xKVLevel.Attributes["Name"].Value);
                        foreach (String TypeToAdd in "Energized,PartiallyEnergized,DeEnergized".Split(','))
                        {
                            oCmd.Parameters.Add(TypeToAdd.Substring(0, 1) + "1", ColorTranslator.FromHtml(xKVLevel[TypeToAdd].Attributes["ForeColor"].Value).ToArgb());
                            oCmd.Parameters.Add(TypeToAdd.Substring(0, 1) + "2", float.Parse(xKVLevel[TypeToAdd].Attributes["Width"].Value));
                            oCmd.Parameters.Add(TypeToAdd.Substring(0, 1) + "3", XmlConvert.ToBoolean(xKVLevel[TypeToAdd].Attributes["Blink"].Value) ? 1 : 0);
                        } using (OracleDataReader oRd = oCmd.ExecuteReader())
                            ModifiedRows += oRd.RecordsAffected;
                    }
            }
            else
                foreach (XmlElement xTable in xDoc.DocumentElement.ChildNodes)
                    foreach (XmlElement xRow in xTable.ChildNodes)
                    {
                        bool FirstRow = true;
                        int CurNumber = 0;
                        StringBuilder OutStr = new StringBuilder("INSERT INTO " + xTable.Attributes["Name"].Value + " ");
                        StringBuilder OutStr2 = new StringBuilder(") VALUES ");
                        Dictionary<String, Object> Parameters = new Dictionary<string, object>();
                        foreach (XmlAttribute xAttr in xRow.Attributes)
                        {
                            OutStr.Append((FirstRow ? "(\"" : ",\"") + xAttr.Name + "\"");
                            OutStr2.Append((FirstRow ? "(:P" : ",:P") + (++CurNumber));                            
                            if (xAttr.Value.EndsWith("="))
                                Parameters.Add("P" + CurNumber, Convert.FromBase64String(xAttr.Value));
                            else if (xAttr.Name.Equals("Coordinates", StringComparison.CurrentCultureIgnoreCase))
                                Parameters.Add("P" + CurNumber, Convert.FromBase64String(xAttr.Value));
                            else
                                Parameters.Add("P" + CurNumber, xAttr.Value);
                            FirstRow = false;
                        }

                        //Now, write our command
                        using (OracleCommand oCmd = new OracleCommand(OutStr.ToString() + OutStr2.ToString() + ")", oConn))
                        {
                            oCmd.Prepare();
                            foreach (KeyValuePair<String, Object> Param in Parameters)
                                oCmd.Parameters.Add(Param.Key, Param.Value);

                            using (OracleDataReader oRd = oCmd.ExecuteReader())
                                ModifiedRows += oRd.RecordsAffected;
                        }
                    }
            
            return ModifiedRows;
        }

        /// <summary>
        /// Backup all tables into a schema
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="TablesToBackup"></param>
        public void BackupSchema(string FileName, string[] TablesToBackup)
        {
            using (FileStream fsOut = new FileStream(FileName, FileMode.Create))
            {
                //Build our XML text writer
                XmlTextWriter xWout;
                if (FileName.EndsWith(".xml.gz", StringComparison.CurrentCultureIgnoreCase))
                    xWout = new XmlTextWriter(new GZipStream(fsOut, CompressionMode.Compress), Encoding.UTF8);
                else
                    xWout = new XmlTextWriter(fsOut, Encoding.UTF8);
                xWout.Formatting = Formatting.Indented;

                //Write our document start
                xWout.WriteStartDocument(true);
                xWout.WriteStartElement("MacomberMap_Backup");

                //Go through every table, and view the results
                foreach (String tblToBack in TablesToBackup)
                {
                    xWout.WriteStartElement("Table");
                    xWout.WriteAttributeString("Name", tblToBack);
                    using (OracleCommand oCmd = new OracleCommand("SELECT * FROM " + tblToBack, oConn))
                    using (OracleDataReader oRd = oCmd.ExecuteReader())
                    {
                        while (oRd.Read())
                        {
                            xWout.WriteStartElement("Row");
                            for (int a = 0; a < oRd.FieldCount; a++)
                                if (oRd[a] is DBNull == false)
                                    if (oRd[a] is byte[])
                                        xWout.WriteAttributeString(oRd.GetName(a),Convert.ToBase64String((byte[])oRd[a]));                                            
                                    else
                                        xWout.WriteAttributeString(oRd.GetName(a), oRd[a].ToString());
                            xWout.WriteEndElement();
                        }                        
                    }
                    xWout.WriteEndElement();
                }

                xWout.WriteEndDocument();
                xWout.Flush();
                xWout.Close();
                //fsOut.Flush();
                fsOut.Close();
                fsOut.Dispose();
                
            }
        }
        #endregion

        /// <summary>
        /// Retrieve the latitude/longitude for a particular substation
        /// </summary>
        /// <param name="Station"></param>
        public void LoadSubstationLatLong(MM_Substation Station)
        {
            using (OracleCommand oCmd = new OracleCommand("SELECT LATITUDE, LONGITUDE, LONGNAME FROM MM_SUBSTATION WHERE NAME=:NAME", oConn))
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("NAME", Station.Name);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                {
                    if (oRd.HasRows)
                    {
                        oRd.Read();
                        Station.LatLong = new PointF(MM_Repository.ToSingle(oRd["LONGITUDE"]), MM_Repository.ToSingle(oRd["LATITUDE"]));
                        Station.LongName = (string)oRd["LONGNAME"];
                    }
                    oRd.Close();
                }
            }
        }

        /// <summary>
        /// Add a new element type into the database
        /// </summary>
        /// <param name="NewType"></param>
        public void AddElementType(MM_Element_Type NewType)
        {
            using (OracleCommand oCmd = new OracleCommand("INSERT INTO MM_ELEMENT_TYPE (INDEX, NAME, ACRONYM, CIMNAME, CIMPARAMETER, ELEMENTPARAMETERS) VALUES (:INDEX, :NAME, :ACRONYM, :CIMNAME, :CIMPARAMETER, :ELEMENTPARAMETERS)", oConn)            )
            {
                oCmd.Prepare();
                oCmd.Parameters.Add("Index", NewType.Index);
                oCmd.Parameters.Add("Name", NewType.Name);
                oCmd.Parameters.Add("Acronym", NewType.Acronym);
                oCmd.Parameters.Add("CIMName", NewType.CIMName);
                oCmd.Parameters.Add("CIMParameter", NewType.CIMParameter);
                oCmd.Parameters.Add("ElementParameters", NewType.ElementParameters);
                using (OracleDataReader oRd = oCmd.ExecuteReader())
                    oRd.Close();
            }
        }
    }
}
